﻿using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Model;
using Newtonsoft.Json;
using NPOI.SS.Formula.Functions;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class BiProductDal
    {
        private readonly AppDbContext _dbContext;
        private string URL;
        public BiProductDal(AppDbContext dbContext)
        {
            dynamic jsonObject = JsonConvert.DeserializeObject(File.ReadAllText("appsettings.json"));
            URL = jsonObject.ConnectionStrings.SqlServer;
            _dbContext = dbContext;
        }
        /// <summary>
        /// 获取所有货品信息（优化版）
        /// </summary>
        public async Task<List<BiProductDetailDTO>> GetAllWithDetailsAsync()
        {
            using (var con = new SqlConnection(URL))
            {
                string sql = @"
                SELECT 
                    p.goods_id as GoodsId,
                    p.goods_code as GoodsCode,
                    p.goods_name as GoodsName,
                    t.goods_type_name as GoodsTypeName,
                    c.customer_name as CustomerName,
                    s.supplier_name as SupplierName,
                    p.specification as Specification,
                    p.purchase_reference_price as PurchaseReferencePrice,
                    p.sales_reference_price as SalesReferencePrice,
                    p.display_order as DisplayOrder,
                    p.status as Status,
                    p.shelf_status as ShelfStatus,
                    p.shelf_life as ShelfLife,
                    p.shelf_life_type as ShelfLifeType,
                    p.warning_days as WarningDays,
                    p.stock_min as StockMin,
                    p.stock_max as StockMax,
                    p.below_min as BelowMin,
                    p.above_max as AboveMax,
                    p.goods_image_path as GoodsImagePath,
                    p.attachment_id as AttachmentId,
                    p.is_delete as IsDelete,
                    u.users_true_name as CreateBy,
                    p.create_time as CreateTime,
                    u.users_true_name as UpdateBy,
                    p.update_time as UpdateTime,
                    p.remarks as Remarks,
                    ISNULL(SUM(i.available_inventory_qty), 0) as CurrentStock
                FROM bi_product p
                LEFT JOIN bi_product_category t ON p.goods_type_id = t.goods_type_id
                LEFT JOIN bi_customer c ON p.customer_id = c.customer_id
                LEFT JOIN bi_supplier s ON p.supplier_id = s.supplier_id
                LEFT JOIN wh_inventory i ON p.goods_id = i.goods_id
                LEFT JOIN sys_users u ON p.update_by = u.users_id
                WHERE p.is_delete = '0'
                GROUP BY 
                    p.goods_id, p.goods_code, p.goods_name, t.goods_type_name, c.customer_name, s.supplier_name,
                    p.specification, p.purchase_reference_price, p.sales_reference_price, p.display_order, p.status,
                    p.shelf_status, p.shelf_life, p.shelf_life_type, p.warning_days, p.stock_min, p.stock_max,
                    p.below_min, p.above_max, p.goods_image_path, p.attachment_id, p.is_delete, u.users_true_name,
                    p.create_time, u.users_true_name, p.update_time, p.remarks
                ORDER BY p.create_time DESC";

                return (await con.QueryAsync<BiProductDetailDTO>(sql)).ToList();
            }
        }
        /// <summary>
        /// 根据ID获取所有货品信息（优化版）
        /// </summary>
        public async Task<List<BiProductDetailDTO>> GetAllWithDetailsIdAsync(long id)
        {
            using (var con = new SqlConnection(URL))
            {
                string sql = @"
                SELECT 
                    p.goods_id as GoodsId,
                    p.goods_code as GoodsCode,
                    p.goods_name as GoodsName,
                    t.goods_type_name as GoodsTypeName,
                    c.customer_name as CustomerName,
                    s.supplier_name as SupplierName,
                    p.specification as Specification,
                    p.purchase_reference_price as PurchaseReferencePrice,
                    p.sales_reference_price as SalesReferencePrice,
                    p.display_order as DisplayOrder,
                    p.status as Status,
                    p.shelf_status as ShelfStatus,
                    p.shelf_life as ShelfLife,
                    p.shelf_life_type as ShelfLifeType,
                    p.warning_days as WarningDays,
                    p.stock_min as StockMin,
                    p.stock_max as StockMax,
                    p.below_min as BelowMin,
                    p.above_max as AboveMax,
                    p.goods_image_path as GoodsImagePath,
                    p.attachment_id as AttachmentId,
                    p.is_delete as IsDelete,
                    u.users_true_name as CreateBy,
                    p.create_time as CreateTime,
                    u.users_true_name as UpdateBy,
                    p.update_time as UpdateTime,
                    p.remarks as Remarks,
                    ISNULL(SUM(i.available_inventory_qty), 0) as CurrentStock
                FROM bi_product p
                LEFT JOIN bi_product_category t ON p.goods_type_id = t.goods_type_id
                LEFT JOIN bi_customer c ON p.customer_id = c.customer_id
                LEFT JOIN bi_supplier s ON p.supplier_id = s.supplier_id
                LEFT JOIN wh_inventory i ON p.goods_id = i.goods_id
                LEFT JOIN sys_users u ON p.update_by = u.users_id
                WHERE p.is_delete = '0' and p.goods_id = @id
                GROUP BY 
                    p.goods_id, p.goods_code, p.goods_name, t.goods_type_name, c.customer_name, s.supplier_name,
                    p.specification, p.purchase_reference_price, p.sales_reference_price, p.display_order, p.status,
                    p.shelf_status, p.shelf_life, p.shelf_life_type, p.warning_days, p.stock_min, p.stock_max,
                    p.below_min, p.above_max, p.goods_image_path, p.attachment_id, p.is_delete, u.users_true_name,
                    p.create_time, u.users_true_name, p.update_time, p.remarks
                ORDER BY p.create_time DESC";

                return (await con.QueryAsync<BiProductDetailDTO>(sql, new { id })).ToList();
            }
        }
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="data">实体</param>
        /// <returns>结果</returns>
        public async Task<int> CreateAsync(BiProduct data)
        {
            // 新增实体到数据库集
            _dbContext.BiProduct.Add(data);
            // 保存更改
            return await _dbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 根据ID查询
        /// </summary>
        /// <param name="Id">ID</param>
        /// <returns>用户列表</returns>
        public async Task<BiProduct> GetByIdAsync(long Id)
        {
            // 根据ID查询
            return await _dbContext.BiProduct.FirstOrDefaultAsync(g => g.GoodsId == Id);
        }
        /// <summary>
        /// 根据编号查询
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<BiProduct> GetByCodeAsync(string id)
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = $"select * from [dbo].[bi_product] where [goods_code]=@id";
                var dat = (await con.QueryAsync<BiProduct>(sql, new { id })).FirstOrDefault();
                return dat;
            }
        }
        /// <summary>
        /// 根据货品类型id查询
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public async Task<GoodsType> GetByTypeIdAsync(long Id)
        {
            // 根据ID查询
            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = $"select * from bi_product_category where [goods_type_id]={Id}";
                return (await con.QueryAsync<GoodsType>(sql)).FirstOrDefault();
            }
        }
        /// <summary>
        /// 库存查询方法，关联库位信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<List<WhInventoryDa>> GetInventoryByGoodsId(long id)
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = @"
                    SELECT 
                        w.WarehouseName as WarehouseName,
	                    w.WarehouseCapacity as warehouseCapacity,
	                    w.WarehouseCode as warehouseCode,
                        l.storage_location_name as LocationName,
                        l.StorageLocationCode as LocationCode,
	                    l.storage_location_capacity as StorageLocationCapacity,
	                    i.*
                    FROM wh_inventory i
                    LEFT JOIN bi_warehouse w ON i.WarehouseId = w.WarehouseId
                    LEFT JOIN wh_location l ON i.StorageLocationId = l.StorageLocationId
                    WHERE i.goods_id = @id";

                return (await con.QueryAsync<WhInventoryDa>(sql, new { id })).ToList();
            }
        }
        /// <summary>
        /// 查询所有
        /// </summary>
        /// <returns>用户列表</returns>
        public async Task<List<BiProduct>> GetAllAsync()
        {
            // 查询所有并转换为列表
            return await _dbContext.BiProduct.ToListAsync();
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="date">包含更新信息</param>
        public async Task<int> UpdateAsync(BiProduct date)
        {
            // 标记实体为修改状态
            _dbContext.BiProduct.Update(date);
            // 保存更改
            return await _dbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 根据ID删除
        /// </summary>
        /// <param name="Id">ID</param>
        public async Task<int> DeleteAsync(long Id)
        {
            // 先查询实体
            var data = await GetByIdAsync(Id);
            if (data != null)
            {
                // 移除实体
                _dbContext.BiProduct.Remove(data);
                // 保存更改
                return await _dbContext.SaveChangesAsync();
            }
            return 0;
        }

        /// <summary>
        /// 根据id获取客户
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<BiCustomer> GetBiCustomersId(long id)
        {
            // 根据ID查询
            return await _dbContext.biCustomer.FirstOrDefaultAsync(g => g.CustomerId == id);
        }

        /// <summary>
        /// 根据id获取供应商
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<BiSupplier> GetBiSuppliersId(long id)
        {
            // 根据ID查询
            return await _dbContext.biSupplier.FirstOrDefaultAsync(g => g.SupplierId == id);
        }
        // 添加获取所有仓库及库位的方法
        public async Task<List<WarehouseWithLocationsDTO>> GetAllWithLocationsAsync()
        {
            using (var con = new SqlConnection(URL))
            {
                string sql = @"
            SELECT 
                w.WarehouseId, w.WarehouseCode, w.WarehouseName, 
                w.WarehouseAddress, w.WarehouseCapacity,
                l.StorageLocationId, l.StorageLocationCode, 
                l.storage_location_name, l.storage_location_capacity
            FROM bi_warehouse w
            LEFT JOIN wh_location l ON w.WarehouseId = l.WarehouseId
            WHERE w.is_delete = '0'
            ORDER BY w.sort_order, l.sort_order";

                var data = await con.QueryAsync<dynamic>(sql);

                // 分组处理结果
                var result = new Dictionary<long, WarehouseWithLocationsDTO>();

                foreach (var item in data)
                {
                    long warehouseId = item.warehouse_id;

                    if (!result.ContainsKey(warehouseId))
                    {
                        result[warehouseId] = new WarehouseWithLocationsDTO
                        {
                            WarehouseId = warehouseId,
                            WarehouseCode = item.warehouse_code,
                            WarehouseName = item.warehouse_name,
                            WarehouseAddress = item.warehouse_address,
                            WarehouseCapacity = item.warehouse_capacity,
                            Locations = new List<LocationDTO>()
                        };
                    }

                    if (item.storage_location_id != null)
                    {
                        result[warehouseId].Locations.Add(new LocationDTO
                        {
                            StorageLocationId = item.storage_location_id,
                            StorageLocationCode = item.storage_location_code,
                            storage_location_name = item.storage_location_name,
                            storage_location_capacity = item.storage_location_capacity
                        });
                    }
                }

                return result.Values.ToList();
            }
        }
        /// <summary>
        /// 启用停用状态变更
        /// </summary>
        /// <param name="status"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<int> Status(string status, long id)
        {
           using(SqlConnection con=new SqlConnection(URL))
            {
                string sql = "UPDATE [dbo].[bi_product] SET [status]=@status WHERE [goods_id]=@id";
                return (await con.ExecuteAsync(sql, new { status = status, id = id }));
            }
        }
    }
}